using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
//using Printing.DBUtility;

namespace MINERP.DAL.PinBan
{
    public class DALNewboard
    {
        public DataSet GetPaperType()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "select * from paperType ";
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            DataSet dsPaperType = null;
            dsPaperType = db.ExecuteDataSet(dbCommand);
            return dsPaperType;
        }

        public DataSet GetPaperFace()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "select * from paperface ";
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            DataSet dsPaperFace = null;
            dsPaperFace = db.ExecuteDataSet(dbCommand);
            return dsPaperFace;
        }


        //public DataSet GetPaperFace()
        //{
        //    string sqlText = "select * from tb_paperface ";

        //    DataSet ds=SqlHelper.GetDS(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlText); ;

        //    return ds;
        //}

        //public DataSet GetPaperType()
        //{
        //    string sqlText = "select * from tb_papertype ";

        //    DataSet ds = SqlHelper.GetDS(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlText); ;

        //    return ds;
        //}


        public DataTable GetPaperTemplate()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "select a.*,pf.name paperFaceName,pt.name paperTypeName from tb_paperTemplate a left join paperType pt on a.paperTypeId=pt.paperTypeId left join paperFace pf on a.paperFaceId=pf.paperFaceId ";
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            DataTable dt = new DataTable();
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                dt.Load(dr);
            }

            return dt;
        }

        public void AddNewTemplate(string templateName, int paperTypeid, int paperFaceId, double boardLength, double boardHeight)
        {
            Database db = DatabaseFactory.CreateDatabase();
            //string sqlCommand = "insert into tb_paperTemplate values(@TemplateName,@paperTypeId,@paperFaceId,@boardLegth)";
            //DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            DbCommand dbCommand = db.GetStoredProcCommand("proc_AddNewPaperTemplate");
            db.AddInParameter(dbCommand, "@TemplateName", DbType.String, templateName);
            db.AddInParameter(dbCommand, "@paperTypeId", DbType.Int32, paperTypeid);
            db.AddInParameter(dbCommand, "@paperFaceId", DbType.Int32, paperFaceId);
            db.AddInParameter(dbCommand, "@boardLegth", DbType.Double, boardLength);
            db.AddInParameter(dbCommand, "@boardHeight", DbType.Double, boardHeight);

            try
            {
                db.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        public DataTable GetBoardWidthByPTypeId(int paperTypeId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            //string sqlCommand = "insert into tb_paperTemplate values(@TemplateName,@paperTypeId,@paperFaceId,@boardLegth)";
            //DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            DbCommand dbCommand = db.GetStoredProcCommand("proc_GetBoardWidthByPTypeId");
            db.AddInParameter(dbCommand, "@PTypeId", DbType.Int32, paperTypeId);

            DataSet ds = new DataSet();
            ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables[0];

        }

        public bool DelTemp(int TempId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM tb_paperTemplate WHERE TempLateId =" + TempId);
            try
            {
                db.ExecuteNonQuery(dbCommand);
            }
            catch (Exception)
            {

                return false;
            }
            return true;
        }
    }
}
